package com.vf.s.mvc.biz.service;

import java.io.File;
import java.io.FileInputStream;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.vf.s.common.model.biz.BizVideo;
 
//读取exlcel数据
public class ReadVideoService  {
	
	private String file = "D:\\test.xls";
 
	
	public  List<BizVideo> list  ;
	
	public ReadVideoService(String filename )
	{
		file = filename; 
	}
	 
	
	public String Read() throws Exception
	{
		list =new ArrayList<BizVideo>();
		File excelFile = new File(file);
		Workbook wb;
		Sheet sheet ;
		if(file.indexOf(".xlsx")>0)
			wb=new XSSFWorkbook(new FileInputStream(excelFile));
		else
			wb=new HSSFWorkbook(new FileInputStream(excelFile));
		sheet= wb.getSheetAt(0);
		Row rowhead = sheet.getRow(0);
   
		String checkResult = HeaderCheck(rowhead);
        if(checkResult.length()>0  )
        	return checkResult;
        
    	int recordcount = sheet.getLastRowNum();
    	 
    	for (int i = 1; i < recordcount+1; i++)
    	{
    		try {
	    		 
	    		Row row = sheet.getRow(i);
	    		String cell0= GetStringValue(row.getCell(0)) ;
	    		//如果行为空或者行第一列为空，则退出
	    		 if (row == null ||  cell0==null || cell0.length()==0) {
	    			 break;
	    		 }
	    		 
	    		 BizVideo record = new BizVideo();
	    		 record.setName(GetStringValue(row.getCell(0)));
	    		 record.setCode(GetStringValue(row.getCell(1)));
	    		 record.setClassifyId(GetStringValue(row.getCell(2)));
	    		 record.setGroupName(GetStringValue(row.getCell(3)));
	    		 record.setType("webRTC");
	    		 
	    		 record.setAspectRatio(GetStringValue(row.getCell(4)));
 	    		 record.setIp(GetStringValue(row.getCell(5)));
	    		 record.setUsername(GetStringValue(row.getCell(6)));
	    		 record.setPassword(GetStringValue(row.getCell(7)));
	    		 
	    		 record.setPort(GetStringValue(row.getCell(8)));
	    		 record.setChannelNum(GetStringValue(row.getCell(9)));
	    		 record.setDeviceType(GetStringValue(row.getCell(10)));
	    		 record.setDeviceStatus(GetStringValue(row.getCell(11)));
	    		 
	    		 record.setMonitorType(GetStringValue(row.getCell(12)));
	    		 record.setProtocol(GetStringValue(row.getCell(13)));
	    		 record.setUrl(GetStringValue(row.getCell(14)));
	    		 
	    		 if(record.getName()==null || record.getName().length()==0) {
	    			 return "第"+(i+1)+"行【设备名称】为空";
	    		 }
	    		 if(record.getAspectRatio()==null || record.getAspectRatio().length()==0) {
	    			 return "第"+(i+1)+"行【画面宽高比】为空";
	    		 }
	    		 if(record.getIp()==null || record.getIp().length()==0) {
	    			 return "第"+(i+1)+"行【ip】为空";
	    		 }
	    		 if(record.getUsername()==null || record.getUsername().length()==0) {
	    			 return "第"+(i+1)+"行【用户名】为空";
	    		 }
	    		 if(record.getPassword()==null || record.getPassword().length()==0) {
	    			 return "第"+(i+1)+"行【密码】为空";
	    		 }
	    		 if(record.getPort()==null || record.getPort().length()==0) {
	    			 return "第"+(i+1)+"行【端口】为空";
	    		 }
	    		 if(record.getDeviceType()==null || record.getDeviceType().length()==0) {
	    			 return "第"+(i+1)+"行【设备类型】为空";
	    		 }
	    		 if(record.getProtocol()==null || record.getProtocol().length()==0) {
	    			 return "第"+(i+1)+"行【协议类型】为空";
	    		 }
	    		 if(!"rtsp".equals(record.getProtocol()) &&  !"onvif".equals(record.getProtocol())) {
	    			 return "第"+(i+1)+"行【协议类型】错误，支持协议【rtsp】或者【onvif】";
	    		 }
	    		 
	    		 if(!"0".equals(record.getDeviceStatus()) &&  !"1".equals(record.getDeviceStatus())) {
	    			 return "第"+(i+1)+"行【设备状态】错误，状态为【0】或者【1】，0标识在线，1标识离线。";
	    		 }
	    		 
	    		 list.add(record);
    		 
    		}
	    	catch(Exception ex) {
	    		System.out.println(ex);
	    		throw new Exception("第"+(i+1)+"行数据读取异常！");
	    	}
    	
    	}
    	
		return "";
	}
	
	private String HeaderCheck(Row rowhead)
	{
		if( rowhead.getLastCellNum()<15)
        	return "表头格式不对,数据列不够";
		 
        //表头检查
    	for (int i = 0; i < 15; i++)
    	{
    		String headername = rowhead.getCell(i).getStringCellValue();
			switch (i) {
			case 0:
				if (headername.indexOf("设备名称") < 0 )
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 1:
				if (headername.indexOf("设备标识") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 2:
				if (headername.indexOf("设备分类") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 3:
				if (headername.indexOf("上级节点名称") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 4:
				if (headername.indexOf("画面宽高比") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 5:
				if (headername.indexOf("ip") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 6:
				if (headername.indexOf("用户名") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 7:
				if (headername.indexOf("密码") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 8:
				if (headername.indexOf("端口") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 9:
				if (headername.indexOf("通道号") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 10:
				if (headername.indexOf("设备类型") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 11:
				if (headername.indexOf("设备状态") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 12:
				if (headername.indexOf("监控点类型") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 13:
				if (headername.indexOf("协议类型") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			case 14:
				if (headername.toLowerCase().indexOf("安装地址") < 0)
					return "表头第" + (i + 1) + "列格式不对";
				break;
			 
			}
    	}
    	return "";
	}
	
	
	 private String GetStringValue(Cell cell  ) throws Exception
	 {
		 if (cell.getCellType() == 0)
         {
			 DecimalFormat df = new DecimalFormat("0.##");
			 df.setRoundingMode(RoundingMode.HALF_UP);
			 return df.format(cell.getNumericCellValue());
         }
         else if (cell.getCellType() == 3)//空数据类型
         {
             return "";
         }
         else if (cell.getCellType() == 2)//公式类型
         {
              throw new Exception("表格中存在公式，无法识别！");
         }
         else //其他类型都按字符串类型来处理
         {
            return  cell.getStringCellValue() ;
         }
	 }
	 
	 private Double GetdoubleValue(Cell cell  ) throws Exception
	 {
		 if (cell.getCellType() == 0)
         {
             return cell.getNumericCellValue();
         }
         else if (cell.getCellType() == 3)//空数据类型
         {
             return null;
         }
         else if (cell.getCellType() == 2)//公式类型
         {
              return 0d;
         }
         else //其他类型都按字符串类型来处理
         {
             String tmp = cell.getStringCellValue() ;
             if (tmp==null || tmp.length()==0)
                 return 0d;
             return Double.parseDouble(tmp);
         }
	 }
	 
	 
	
}
